"""pdf"""
import os
import base64
import re
from datetime import datetime
import uuid
import camelot
from werkzeug.utils import secure_filename
import fitz
from flask import Blueprint, request, g
import pytz
from util import ensure_path
from util.response import response
from dao.project import project_exists

pdf_api = Blueprint('pdf_api', __name__)


@pdf_api.route("/api/pdf/import", methods=['POST'])
def pdf_import():
    """pdf import """
    project_id = request.form.get('project_id', type=int)

    if project_id is not None:
        try:
            _ = int(project_id)
        except ValueError:
            return response(500, "项目ID有误", {})
        
    # 返回json数据的方法
    file = request.files['file']
    result = {
        "info": {},
        "result_list": []
    }

    if not file:
        return response(422, "未上传文件", {})

    fname = secure_filename(file.filename)
    ext = fname.rsplit('.')[-1]
    # 生成一个uuid作为文件名
    file_name = str(uuid.uuid4()) + "." + ext

    path_dir = ensure_path.get_upload_dir()
    file_path = os.path.join(path_dir, file_name)

    file.save(file_path)
    tables = camelot.read_pdf(filepath=file_path, pages="all")

    if tables.n <= 0:
        return response(500, "pdf 解析错误", {})

    flag = tables[0].data[0][0].replace('\n', '').replace(':', '').replace(' ', '')
    # 发改委 pdf 解析
    if flag in ["登记表编号"]:
        result = fgw_pdf_parse(result, tables)
        # 生成二维码
        qrcode_url = mu_extract_images(file_path)
        result["info"]["qrcode"] = qrcode_url
    # 焦煤 pdf 解析
    elif flag in ["项目信息"]:
        result = jm_pdf_parse(result, tables)
        result["info"]["qrcode"] = ""
    else:
        return response(500, "pdf 解析错误", {})
    import_db(result, int(project_id))
    os.remove(file_path)
    return response(0, "success", {})


@pdf_api.route("/api/pdf/parse", methods=['POST'])
def pdf_parse():
    """返回json数据的方法"""
    file = request.files['file']
    result = {
        "info": {},
        "result_list": []
    }
    if not file:
        return response(422, "未上传文件", {})

    fname = secure_filename(file.filename)
    ext = fname.rsplit('.')[-1]
    # 生成一个uuid作为文件名
    file_name = str(uuid.uuid4()) + "." + ext

    path_dir = ensure_path.get_upload_dir()
    file_path = os.path.join(path_dir, file_name)

    file.save(file_path)
    tables = camelot.read_pdf(filepath=file_path, pages="all")

    if tables.n > 0 and tables[0]:
        flag = tables[0].data[0][0].replace('\n', '').replace(':', '').replace(' ', '')
        # 发改委 pdf 解析
        if flag in ["登记表编号"]:
            result = fgw_pdf_parse(result, tables)
            # 生成二维码
            qrcode_url = mu_extract_images(file_path)
            result["info"]["qrcode"] = qrcode_url
        # 焦煤 pdf 解析
        elif flag in ["项目信息"]:
            result = jm_pdf_parse(result, tables)
            result["info"]["qrcode"] = ""
        else:
            return response(500, "pdf 解析错误", {})

    if result["info"]["register_number"] == "":
        return response(400, "未识别到项目编号", {})

    rowcount = project_exists(result['info']["register_number"],
                                int(datetime.now(pytz.timezone('Asia/Shanghai')).timestamp()) - 86400)

    if rowcount != 0:
        return response(422, "项目已存在且创建时间未大于24小时", {})

    write_db(result)

    os.remove(file_path)
    return response(0, "success", {})
  

def fgw_pdf_parse(result, tables):
    """发改委 pdf 解析"""
    cleaned_data = [[item.replace('\n', '').replace(':', '').replace(' ', '') for item in row] for row in
                    tables[0].data]

    for inner_list in cleaned_data:
        for idx, item in enumerate(inner_list):
            if item == "登记表编号":
                num = inner_list[idx + 1]
                if num == "":
                    num = inner_list[idx + 2]
                result["info"]["register_number"] = num
            if "标段包" in item:
                result["info"]["project_name"] = inner_list[idx + 1]

    for idx in range(1, tables.n):
        if tables.n >= idx and tables[idx]:
            cleaned_data1 = [[item.replace('\n', '').replace(':', '').replace(' ', '') for item in row] for row in
                             tables[idx].data]
            for inner_list in cleaned_data1:
                if inner_list[0] in ["抽取结果", "验证码"]:
                    continue
                if inner_list[0] == "":
                    continue
                # 不是数字字符串
                if not inner_list[0].isdigit():
                    continue

                if inner_list[4] != "":
                    work_unit = inner_list[4]
                else:
                    work_unit = inner_list[3]

                idcard_number = inner_list[2][:18]
                if idcard_number == "":
                    pattern = r'\d{17}[\dXx]'
                    # 在字符串中搜索匹配
                    match = re.search(pattern, work_unit)
                    if match:
                        idcard_number = match.group(0)
                        work_unit = work_unit.replace(str(idcard_number), "")

                result["result_list"].append({
                    "verify_code": inner_list[0],
                    "name": inner_list[1],
                    "idcard_number": idcard_number.upper(),
                    "work_unit": work_unit.replace("男", "").replace("女", ""),
                })
    return result


def jm_pdf_parse(result, tables):
    """焦煤 pdf 解析"""
    cleaned_data = []
    for idx in range(0, tables.n):
        tmp_list = [[item.replace('\n', '').replace(':', '').replace(' ', '') for item in row] for row in
                   tables[idx].data]
        for item in tmp_list:
            cleaned_data.append(item)
    page = 1
    for idx, inner_list in enumerate(cleaned_data):
        if inner_list[0] == "项目编号":
            num = ""
            if len(inner_list) > 2 and inner_list[2] != "":
                num = inner_list[2]
            elif inner_list[1] != "":
                num = inner_list[1]
            result["info"]["register_number"] = num
        if inner_list[0] == "项目名称":
            name = ""
            if len(inner_list) > 2 and inner_list[2] != "":
                name = inner_list[2]
            elif inner_list[1] != "":
                name = inner_list[1]
            result["info"]["project_name"] = name

        if "验证码" in inner_list[0]:
            page = 2
            continue
        if page >= 2:
            if inner_list[0] == "" and inner_list[1] == "":
                continue

            if inner_list[4] == "":
                work_unit = inner_list[5]
            else:
                work_unit = inner_list[4]

            result["result_list"].append({
                "verify_code": inner_list[0],
                "name": inner_list[1],
                "idcard_number": inner_list[2][:18].upper(),
                "work_unit": work_unit,
                # "性别": inner_list[3],
                # "单位": inner_list[4],
                # "所在地市": inner_list[5],
                # "职称级别": inner_list[6],
                # "专业": inner_list[7],
            })

    return result


def mu_extract_images(pdf_file_path):
    """使用fitz 库直接提取pdf的图像"""
    # 打开pdf，打印PDF的相关信息
    doc = fitz.open(pdf_file_path)
    # 图片计数
    imgcount = 0
    # 遍历doc，获取每一页
    encoded_string = ""
    for page in doc:
        try:
            imgcount += 1
            imgcount2 = 0
            tuple_image = page.get_images()
            for xref in list(tuple_image):
                imgcount2 += 1
                if imgcount2 >= 3:
                    break
                xref = list(xref)[0]
                img = doc.extract_image(xref)  # 获取文件扩展名，图片内容 等信息
                if img["ext"] == "jpeg" or img["width"] == img["height"]:
                    data = base64.b64encode(img["image"]).decode()
                    encoded_string = f"data:image/{img['ext']};base64,{data}"
                else:
                    continue
        except:
            continue

    return encoded_string


def import_db(result, new_project_id):
    """import db"""
    with g.db_conn.cursor() as cursor:
        current_timestamp = int(datetime.now().timestamp())

        install_person_query = "INSERT INTO persons (name, id_card, work_org, created_at) VALUES (%s, %s, %s, %s) RETURNING id"

        exists_query = "SELECT EXISTS (SELECT 1 FROM persons WHERE id_card = %s)"

        update_person_query = "UPDATE persons SET name = %s, work_org = %s, updated_at = %s WHERE id_card = %s RETURNING id"
        
        install_relation_query = """
            INSERT INTO 
                project_person_relation(person_id, project_id, verify_code, created_at, role_id) 
            VALUES (%s, %s, %s, %s, %s) 
            RETURNING id
        """
        
        exists_relation_query = """
            SELECT EXISTS (
                SELECT 
                    1 
                FROM 
                    project_person_relation 
                WHERE 
                    person_id = %s AND project_id = %s AND deleted_at IS NULL
            )
        """

        for _, item in enumerate(result["result_list"]):

            cursor.execute(exists_query, [item["idcard_number"]])
            result = cursor.fetchone()[0]

            if result:
                cursor.execute(update_person_query, (
                    item["name"],
                    item["work_unit"],
                    current_timestamp,
                    item["idcard_number"],
                ))
                new_person_id = cursor.fetchone()[0]
            else:
                cursor.execute(install_person_query, (
                    item["name"],
                    item["idcard_number"],
                    item["work_unit"],
                    current_timestamp,
                ))
                new_person_id = cursor.fetchone()[0]

            cursor.execute(exists_relation_query, (new_person_id, new_project_id))
            result = cursor.fetchone()[0]
            if not result:
                cursor.execute(install_relation_query, (
                    new_person_id,
                    new_project_id,
                    item["verify_code"],
                    current_timestamp,
                    1,
                ))

            g.db_conn.commit()


def write_db(result):
    """write db"""
    with g.db_conn.cursor() as cursor:
        current_timestamp = int(datetime.now(pytz.timezone('Asia/Shanghai')).timestamp())

        install_project_query = """
            INSERT INTO 
                projects(project_name, project_code, qrcode_base64, created_at) 
                VALUES(%s, %s, %s, %s) 
            RETURNING id
        """

        install_person_query = """
            INSERT INTO 
                persons(name, id_card, work_org, created_at) 
                VALUES(%s, %s, %s, %s) 
            RETURNING id
        """

        install_relation_query = """
            INSERT INTO 
                project_person_relation(person_id, project_id, verify_code, created_at, role_id) 
                VALUES(%s, %s, %s, %s, %s) 
            RETURNING id
        """

        exists_relation_query = """
            SELECT EXISTS (
                SELECT 
                    1 
                FROM 
                    project_person_relation 
                WHERE 
                    person_id = %s AND project_id = %s AND deleted_at IS NULL
            )
        """
        
        cursor.execute(install_project_query, (
            result['info']["project_name"],
            result['info']["register_number"],
            result['info']['qrcode'],
            current_timestamp,
        ))

        exists_query = "SELECT EXISTS (SELECT 1 FROM persons WHERE id_card = %s)"

        update_person_query = """UPDATE persons SET name = %s, work_org = %s, updated_at = %s WHERE id_card = %s RETURNING id"""

        new_project_id = cursor.fetchone()[0]

        for _, item in enumerate(result["result_list"]):

            cursor.execute(exists_query, [item["idcard_number"]])
            result = cursor.fetchone()[0]

            if result:
                cursor.execute(update_person_query, (
                    item["name"],
                    item["work_unit"],
                    current_timestamp,
                    item["idcard_number"],
                ))
                new_person_id = cursor.fetchone()[0]
            else:
                cursor.execute(install_person_query, (
                    item["name"],
                    item["idcard_number"],
                    item["work_unit"],
                    current_timestamp,
                ))
                new_person_id = cursor.fetchone()[0]

            cursor.execute(exists_relation_query, (new_person_id, new_project_id))
            result = cursor.fetchone()[0]
            if not result:
                cursor.execute(install_relation_query, (
                    new_person_id,
                    new_project_id,
                    item["verify_code"],
                    current_timestamp,
                    1,
                ))

            g.db_conn.commit()
